package com.campanyes.camp2011;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import com.campanyes.entitites.Configuracion;
import com.campanyes.entitites.ConsultasBBDD;
import com.campanyes.entitites.Forestal2012;
import com.campanyes.entitites.Scripts;
import com.campanyes.entitites.Utils;

public class Leer_PROV_TOTALS_PUNTUACIO_GIRONA {
	
	private static final Logger logger = Logger.getLogger(Leer_PROV_TOTALS_PUNTUACIO_GIRONA.class);
	
	public void runMain(String [] args) throws BiffException, IOException, ClassNotFoundException, SQLException {
		
		PropertyConfigurator.configure(args[0]+args[2]);
		Configuracion conf = new Configuracion(args[0], args[1]);

		Class.forName(conf.getBBDD_DRIVER());		
		Connection conexion = DriverManager.getConnection ("jdbc:mysql://"+conf.getBBDD_HOST()+"/"+conf.getBBDD_DATABASE(),conf.getBBDD_USER(), conf.getBBDD_PASSWORD());
		Statement s = conexion.createStatement();		
		
		ConsultasBBDD consultasBBDD = new ConsultasBBDD(s);
		Scripts scripts = new Scripts(s);
		Leer_PROV_TOTALS_PUNTUACIO_GIRONA leer = new Leer_PROV_TOTALS_PUNTUACIO_GIRONA();
			
		//Borramos resultados antiguos
		scripts.dropTableAOFS2012();
		
		//Crear tabla EPAFS 2011
		scripts.crearTablaAOFS2012();
		
		
		//Leemos el fichero Excel
		Workbook workbook = Workbook.getWorkbook(new File("files/PROV_TOTALS_PUNTUACIO_GIRONA.xls"));		    						
        
		//for(int j=1; j<=workbook.getNumberOfSheets(); j++){
		for(int j=12; j<=16; j++){
			Sheet sheet = workbook.getSheet("Table "+j);  
						
			for(int i=1; i<sheet.getRows(); i++){				
				Forestal2012 aof2012 = leer.getOpositorAOFGirona2012Finals(sheet, i);
				
				System.out.println(aof2012);
				consultasBBDD.insertarAOF2012(aof2012);
			}

		}
		
              
        workbook.close();       
		
        logger.info("FIN DE LA CARGA AOF 2012");
	
	}
	
	private Forestal2012 getOpositorAOFGirona2012Finals(Sheet sheet, int fila){
		Utils utils = new Utils();
		Cell celda = null;
		
		Forestal2012 aof2012 = null;
		try{
			celda = sheet.getCell(1, fila);		
			String auxdni = "0"+celda.getContents();
		    String DNI =  utils.prepararDNI(auxdni);			
		    		    
		    // De la celda partimos para coger nom, cognom1, cognom2 ---------------------------
		    celda= sheet.getCell(2, fila);					
			String aux= utils.prepararString(celda.getContents());
		    
			String cognom1="";		  		   
		    boolean fi=false;
			int finCognom1=0;
			for(int n=0; n<aux.length() && !fi; n++){
				if(aux.charAt(n)!=' '){
					cognom1 += aux.charAt(n);
				}else if(aux.charAt(n+1)!=' '){
					cognom1 += aux.charAt(n);
				}else{
					finCognom1=n;
					fi=true;					
				}
			}
					
			int iniciNom=0;
			String nom="";
			
			fi=false;
			for(int n=aux.length()-1; n>0 && !fi; n--){
				if(aux.charAt(n)!=' '){
					nom = aux.charAt(n) + nom;
				}else if(aux.charAt(n-1)!=' '){
					nom = aux.charAt(n) + nom;
				}else{
					iniciNom=n;
					fi=true;					
				}
			}
			
			String cognom2="";
			cognom2 = (aux.substring(finCognom1, iniciNom)).trim();			    		    
		  		   
		    //--------------------------------------------------------------------
				
			celda= sheet.getCell(3, fila);			
			String nota = celda.getContents();			
			
			celda= sheet.getCell(4, fila);			
			String regio = celda.getContents();					
			
			nom = utils.prepararString(nom);
			cognom1 = utils.prepararString(cognom1);
			cognom2 = utils.prepararString(cognom2);
			 			 
			aof2012 = new Forestal2012(DNI, nom, cognom1, cognom2, nota, regio);	
			
		}catch(Exception e){
			e.printStackTrace();
			System.exit(0);
			//
		}
		
		return aof2012;		
	}
	
}
